package srdb.migration.dbhandler.db2;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import srdb.migration.MTKException;
import srdb.migration.common.IMTKConnection;
import srdb.migration.common.MTKMetaData;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.Constraint;
import srdb.migration.schema.metadata.Index;
import srdb.migration.schema.metadata.Table;

public class MetaData extends MTKMetaData {
	private IMTKConnection conn = null;
	protected String getTablesStr = null;
	protected String getFKConstraintsStr = null;
	protected PreparedStatement getFKConstraints;

	public MetaData(IMTKConnection conn) throws Exception {
		this.conn = conn;
		this.dbConn = conn.getConnection();
	}

	public MetaData(IMTKConnection conn, String targetSchemaName)
			throws Exception {
		this.conn = conn;

		if (conn != null) {
			this.dbConn = conn.getConnection();
		}

		this.targetSchemaName = targetSchemaName;
	}

	@Override
	public boolean supportsSynonyms() {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public int getDBServerMajorVersion() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int getDBServerMinorVersion() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public String getVersion() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected void initSynonymStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initAllPublicSynonymStatement() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initAllPrivateSynonymStatement() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initTableStatement(String commaSepTableNames)
			throws SQLException {
		StringBuilder sb = new StringBuilder(512);
		sb.append("SELECT *");
		sb.append(" FROM SYSCAT.TABLES");
		sb.append(" WHERE TABSCHEMA = ?");
		if (commaSepTableNames != null) {
			sb.append(" AND TABNAME IN (" + commaSepTableNames + ") ");
		}
		sb.append("   AND TYPE IN (");
		sb.append("                  '" + DB2TableType.H.name() + "'");
		sb.append("                 ,'" + DB2TableType.L.name() + "'");
		sb.append("                 ,'" + DB2TableType.T.name() + "'");
		sb.append("                 ,'" + DB2TableType.U.name() + "'");
		sb.append("                 ,'" + DB2TableType.G.name() + "'");
		sb.append("                 )");
		sb.append(" ORDER BY TABNAME");
		sb.append(" WITH UR");

		// this.getTablesStr =
		// "select TABNAME,REMARKS  from SYSCAT.TABLES where TABSCHEMA=?";
		this.getTablesStr = sb.toString();
		// Utility.getLogger().info("getTablesStr: " + this.getTablesStr);
		this.getTables = this.conn.getConnection().prepareStatement(
				this.getTablesStr);
	}

	@Override
	protected void initColumnStatement() throws SQLException {
		this.getColumnsStr = "SELECT * FROM SYSCAT.COLUMNS WHERE TABSCHEMA = ? AND TABNAME = ? ORDER BY COLNO WITH UR";
		this.getColumns = this.conn.getConnection().prepareStatement(
				this.getColumnsStr);

	}

	@Override
	protected void initTriggerStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initViewStatement(String paramString) throws SQLException {
		StringBuilder sb = new StringBuilder(512);
		sb.append("SELECT *");
		sb.append(" FROM SYSCAT.TABLES T");
		sb.append("    , SYSCAT.VIEWS V");
		sb.append(" WHERE V.VIEWSCHEMA = ?");
		sb.append("   AND T.TABSCHEMA = V.VIEWSCHEMA");
		sb.append("   AND T.TABNAME = V.VIEWNAME");
		sb.append("   AND T.TYPE IN (");
		sb.append("                  '" + DB2TableType.V.name() + "'");
		sb.append("                 ,'" + DB2TableType.W.name() + "'");
		sb.append("                 )");
		sb.append(" ORDER BY T.TABNAME");
		sb.append(" WITH UR");

		this.getViewsStr = sb.toString();

		this.getViews = this.conn.getConnection().prepareStatement(
				this.getViewsStr);
	}

	@Override
	protected void initSequenceStatement(String paramString)
			throws SQLException {
		this.getSequencesStr = "SELECT * FROM SYSCAT.SEQUENCES WHERE SEQSCHEMA = ? AND SEQTYPE <> 'A' ORDER BY SEQNAME WITH UR";

		this.getSequences = this.conn.getConnection().prepareStatement(
				this.getSequencesStr);
	}

	@Override
	protected void initProcStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initPackageStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	public static void main(String[] args) {
		StringBuilder sb = new StringBuilder(512);
		sb.setLength(0);
		sb.append(" SELECT R.*");
		// sb.append("      , KCU.COLNAME");
		// sb.append("      , KCU.COLSEQ");
		sb.append("   FROM SYSCAT.REFERENCES R");
		// sb.append("       ,SYSCAT.KEYCOLUSE KCU");
		sb.append("  WHERE R.TABSCHEMA = ?");
		// sb.append("    AND KCU.CONSTNAME = R.CONSTNAME");
		// sb.append("    AND KCU.TABSCHEMA = R.TABSCHEMA");
		// sb.append("    AND KCU.TABNAME   = R.TABNAME");
		sb.append("  ORDER BY R.CONSTNAME");
		// sb.append("         , KCU.COLSEQ");
		sb.append(" WITH UR");
		System.out.println(sb.toString());
	}

	@Override
	protected void initConstraintStatement(String commaSepTableNames)
			throws SQLException {
		StringBuilder sb = new StringBuilder(512);
		sb.setLength(0);
		sb.append(" SELECT C.*");
		sb.append("   FROM SYSCAT.TABCONST C");
		sb.append("  WHERE C.TABSCHEMA = ?");
		sb.append("    AND C.TYPE IN ('P','U')");
		if (commaSepTableNames != null) {
			sb.append(" AND C.TABNAME  IN (" + commaSepTableNames + ") ");
		}
		this.getConstraintsStr = sb.toString();
		sb.setLength(0);
		sb.append(" SELECT C.*");
		// sb.append("      , KCU.TABNAME");
		sb.append("      , KCU.COLNAME");
		sb.append("      , KCU.COLSEQ");
		sb.append("   FROM SYSCAT.TABCONST C");
		sb.append("       ,SYSCAT.KEYCOLUSE KCU");
		sb.append("  WHERE C.TABSCHEMA = ?");
		sb.append("  AND C.CONSTNAME = ?");
		sb.append("  AND C.TABNAME = ?");
		if (commaSepTableNames != null) {
			sb.append(" AND C.TABNAME  IN (" + commaSepTableNames + ") ");
		}
		sb.append("    AND C.TYPE IN ('P','U')");

		sb.append("    AND KCU.CONSTNAME = C.CONSTNAME");
		sb.append("    AND KCU.TABSCHEMA = C.TABSCHEMA");
		sb.append("    AND KCU.TABNAME   = C.TABNAME");
		// sb.append("  ORDER BY C.CONSTNAME");
		// sb.append("         , KCU.COLSEQ");
		// sb.append(" WITH UR");

		this.getConstraintColumnsStr = sb.toString();

		sb.setLength(0);
		sb.append(" SELECT R.*");
		// sb.append("      , KCU.COLNAME");
		// sb.append("      , KCU.COLSEQ");
		sb.append("   FROM SYSCAT.REFERENCES R");
		// sb.append("       ,SYSCAT.KEYCOLUSE KCU");
		sb.append("  WHERE R.TABSCHEMA = ?");
		if (commaSepTableNames != null) {
			sb.append(" AND C.TABNAME  IN (" + commaSepTableNames + ") ");
		}
		// sb.append("    AND KCU.CONSTNAME = R.CONSTNAME");
		// sb.append("    AND KCU.TABSCHEMA = R.TABSCHEMA");
		// sb.append("    AND KCU.TABNAME   = R.TABNAME");
		sb.append("  ORDER BY R.CONSTNAME");
		// sb.append("         , KCU.COLSEQ");
		sb.append(" WITH UR");
		this.getFKConstraintsStr = sb.toString();

		sb.setLength(0);

		// sb.append(" SELECT R.*");
		// sb.append("      , KCU.COLNAME");
		// sb.append("      , KCU.COLSEQ");
		// sb.append("   FROM SYSCAT.REFERENCES R");
		// sb.append("       ,SYSCAT.KEYCOLUSE KCU");
		// sb.append("  WHERE R.TABSCHEMA = ?");
		// sb.append("    AND KCU.CONSTNAME = R.CONSTNAME");
		// sb.append("    AND KCU.TABSCHEMA = R.TABSCHEMA");
		// sb.append("    AND KCU.TABNAME   = R.TABNAME");
		// sb.append("  ORDER BY R.CONSTNAME");
		// sb.append("         , KCU.COLSEQ");
		// sb.append(" WITH UR");
		// SQL_FK_ALL = sb.toString();

		this.getConstraints = this.conn.getConnection().prepareStatement(
				this.getConstraintsStr);
		this.getConstraintColumns = this.conn.getConnection().prepareStatement(
				this.getConstraintColumnsStr);
		this.getFKConstraints = this.conn.getConnection().prepareStatement(
				getFKConstraintsStr);
	}

	protected void getConstraints() throws SQLException {
		this.getConstraints.setString(1, this.schema.getName());
		this.getFKConstraints.setString(1, this.schema.getName());
		// this.getConstraintColumns.setString(2, constraint.getName());
		writeLog("Getting Constraint Definitions");
		ResultSet rs = null;
		ResultSet crs = null;
		ResultSet fkrs = null;
		// Set cons=new HashSet<String>();
		try {
			/**
			 * 迁移主键 唯一限制
			 */
			rs = this.getConstraints.executeQuery();
			String constName = "";
			boolean skipSysColBasedConst = false;
			while (rs.next()) {
				Table constTable = this.schema
						.getTable(rs.getString("TABNAME"));

				if (constTable == null) {
					constTable = getTable(rs.getString("TABNAME"));
					this.schema.addTable(constTable);
				}
				constName = rs.getString("CONSTNAME");
				// if (cons.contains(constName)) {
				//
				// }

				Constraint constraint = new Constraint(constTable, constName,
						false);

				setConstraintColumnsPSParams(constraint);
				crs = this.getConstraintColumns.executeQuery();
				//
				while (crs.next()) {
					constraint.setType(crs.getString("TYPE").charAt(0));
					constraint.setSearchCondition(crs
							.getString("ENABLEQUERYOPT"));
					String constColumn = crs.getString("COLNAME");
					constraint.addColumn(new Column(constColumn));
					try {
						if (!this.schema.hasTableColumn(
								constraint.getTableName(), constColumn)) {
							skipSysColBasedConst = true;
							break;
						}
					} catch (MTKException mtke) {
						mtke.printStackTrace();
					}
					// if ((String.valueOf(rs.getString("TYPE").charAt(0)))
					// .equalsIgnoreCase("F")) {
					// constraint.setRefIntSchemaName(this.schema.getName());
					// constraint
					// .setRefIntegrityTable(rs.getString("REFTABNAME"));
					// constraint.addRefIntegrityColumn(new Column(crs
					// .getString("COLNAME")));
					// }
					// if (isCaseSensitive(constColumn)) {
					// constColumn = "\"" + constColumn + "\"";
					// }

				}

				crs.close();
				if (constraint != null && (!skipSysColBasedConst)) {
					this.schema.addConstraint(constraint);
				}

			}

			/**
			 * 迁移外键
			 */
			fkrs = this.getFKConstraints.executeQuery();
			while (fkrs.next()) {
				Table constTable = this.schema
						.getTable(fkrs.getString("TABNAME"));

				if (constTable == null) {
					constTable = getTable(fkrs.getString("TABNAME"));
					this.schema.addTable(constTable);
				}
				constName = fkrs.getString("CONSTNAME");

				Constraint constraint = new Constraint(constTable, constName,
						false);
				constraint.setType("F".charAt(0));
				// constraint.setSearchCondition(crs
				// .getString("ENABLEQUERYOPT"));
				constraint.addColumn(new Column(fkrs.getString("FK_COLNAMES")));
				constraint.setRefIntSchemaName(this.schema.getName());
				constraint.setRefIntegrityTable(fkrs.getString("REFTABNAME"));
				constraint.addRefIntegrityColumn(new Column(fkrs
						.getString("PK_COLNAMES")));
				String delerule="NO ACTION";
				switch (fkrs.getString("DELETERULE").charAt(0)) {
				case 'A':
					delerule="NO ACTION";
					break;
				case 'C':
					delerule="CASCADE";
					break;
				case 'N':
					delerule="SET NULL";
					break;
				case 'R':
					delerule="RESTRICT";
					break;

				default:
					break;
				}
				constraint.setDeleteRule(delerule);
				this.schema.addConstraint(constraint);
			}

			// int i = 0;
			// for (int count = 1; i < this.schema.getConstraints().size(); i++)
			// {
			// Constraint cons = this.schema.getConstraints().get(i);
			//
			// if (this.schema.getTable(cons.getName()) != null)
			// cons.setName(cons.getName() + "_" + count++);
			//
			// }
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			if (fkrs != null) {
				fkrs.close();
			}

			if (crs != null) {
				crs.close();
			}

			if (rs != null)
				rs.close();
		}
	}

	@Override
	protected void initIndexeStatement(String commaSepTableNames)
			throws SQLException {
		this.getIndexesStr = "SELECT INDNAME,TABNAME,UNIQUERULE,INDEXTYPE,OWNER FROM SYSCAT.INDEXES WHERE INDSCHEMA = ? ";

		this.getIndexColumnsStr = "SELECT COLNAMES, TABNAME FROM SYSCAT.INDEXES WHERE TABSCHEMA = ? AND TABNAME = ? ORDER BY INDNAME WITH UR ";

		if (commaSepTableNames != null) {
			this.getIndexesStr = (this.getIndexesStr + " AND TABNAME IN ("
					+ commaSepTableNames + ") ");
		}

		this.getIndexes = this.conn.getConnection().prepareStatement(
				this.getIndexesStr);
		this.getIndexColumns = this.conn.getConnection().prepareStatement(
				this.getIndexColumnsStr);
	}

	@Override
	protected void initRoleStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initUserStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initGrantStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initDBLinkStatement() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initRulesStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initGroupsStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initDomainsStatement(String paramString) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void initCustomDataTypesStatement(String paramString)
			throws SQLException {
		// TODO Auto-generated method stub

	}

	public void getTables(String commaSepTableNames, boolean importViewAsTable)
			throws SQLException {
		super.getTables(commaSepTableNames, importViewAsTable);

		// if (importViewAsTable) {
		// initViewStatement(commaSepTableNames);
		// getViewsAsTables();
		// }
	}

	@Override
	protected void getTables() throws SQLException {
		writeLog("Getting Table Definitions");
		ResultSet rs = null;
		try {
			this.getTables.setString(1, this.schema.getName());
			rs = this.getTables.executeQuery();
			while (rs.next()) {
				Table table = getTable(rs.getString("TABNAME"));

				if (table != null) {
					table.setComments(rs.getString("REMARKS"));
					this.schema.addTable(table);
				}
			}
		} finally {
			if (rs != null)
				rs.close();
		}
	}

	@Override
	public void parseTables() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public String getParseTablesMsg() throws SQLException {
		return null;
	}

	@Override
	protected void getProcedures() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getFunctions() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getViews() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getPublicSynonyms() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getAllPublicSynonyms() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getAllPrivateSynonyms() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getTriggers() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getSequences() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getPackages() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getIndexes() throws SQLException {

		writeLog("Getting Index Definitions");
		ResultSet rs = null;
		ResultSet irs = null;
		try {
			this.getIndexes.setString(1, this.schema.getName());
			rs = this.getIndexes.executeQuery();

			while (rs.next()) {
				String IndexName = rs.getString("INDNAME");
				String IndexNameOriginal = IndexName;
				boolean isIndexCaseSensitive = false;

				// if (((isIndexCaseSensitive = isCaseSensitive(IndexName))
				// || (!isSimpleIndexName) ? 1 : 0) != 0) {
				// IndexName = "\"" + IndexName + "\"";
				// }

				Index index = new Index(getTable(rs.getString("TABNAME")),
						IndexName);

				index.setIndexTableOwner(rs.getString("OWNER"));
				index.setUniqueness(rs.getString("UNIQUERULE"));
				String indexType = rs.getString("index_type");

				// if (indexType.equalsIgnoreCase("FUNCTION-BASED NORMAL")) {
				// if (this.getFuncBasedIndexColumns == null) {
				// this.getFuncBasedIndexColumns = this.conn
				// .getConnection().prepareStatement(
				// this.getFuncBasedIndexColumnsStr);
				// }
				//
				// this.getFuncBasedIndexColumns.setString(1,
				// this.schema.getName());
				// this.getFuncBasedIndexColumns.setString(2,
				// IndexNameOriginal);
				// irs = this.getFuncBasedIndexColumns.executeQuery();
				// } else {
				this.getIndexColumns.setString(1, this.schema.getName());
				this.getIndexColumns.setString(2, IndexNameOriginal);
				irs = this.getIndexColumns.executeQuery();
				// }

				while (irs.next()) {
					String indCol = irs.getString("COLNAMES");

					// if (indexType.equalsIgnoreCase("FUNCTION-BASED NORMAL"))
					// {
					indCol = indCol.replaceAll("+", "");
					indCol = "(" + indCol + ")";
					// }

					index.addColumn(new Column(indCol));
				}

				irs.close();

				if (index != null)
					this.schema.addIndex(index);
			}
		} finally {
			if (irs != null) {
				irs.close();
			}

			if (rs != null) {
				rs.close();
			}

		}
	}

	@Override
	protected void getRoles() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getUsers() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getGrants() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getRules() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getGroups() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getDomains() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected void getCustomDataTypes() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public String[] getSchemas() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected void getDBLinks() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	protected Table getTable(String TableName) throws SQLException {
		ResultSet rs = null;
		Table table = null;
		try {
			if (this.getColumns == null) {
				initColumnStatement();
			}

			this.getColumns.setString(1, this.schema.getName().toUpperCase());
			this.getColumns.setString(2, TableName.toUpperCase());
			rs = this.getColumns.executeQuery();
			boolean colCaseSensitive = false;

			// boolean isSimpleTableName = Utility
			// .isValidOracleIdentifier(TableName);

			boolean tabCaseSensitive = false;

			// if (((tabCaseSensitive = isCaseSensitive(TableName))
			// || (!isSimpleTableName) ? 1 : 0) != 0) {
			// TableName = "\"" + TableName + "\"";
			// }

			if (table == null) {
				table = new Table(this.schema, TableName, this.targetSchemaName);
			}

			while (rs.next()) {
				String data_default = rs.getString("DEFAULT");

				String colName = rs.getString("COLNAME");

				// if (((colCaseSensitive = isCaseSensitive(colName))
				// || (!isSimpleColumnName) ? 1 : 0) != 0) {
				// colName = "\"" + colName + "\"";
				// }

				Column column = new Column(table, colName);

				column.addDetail(rs.getString("TYPENAME"), rs.getInt("LENGTH"),
						rs.getInt("LENGTH"), rs.getInt("SCALE"),
						rs.getString("NULLS"), colCaseSensitive, data_default,
						rs.getString("REMARKS"), null);
				table.addColumn(column);
			}

			// loadTablePartitions(table);
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

		return table;
	}

	@Override
	public boolean isCaseSensitive(String paramString) {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	protected void setConstraintColumnsPSParams(Constraint constraint)
			throws SQLException {
		this.getConstraintColumns.setString(1, this.schema.getName());
		this.getConstraintColumns.setString(2, constraint.getName());
		this.getConstraintColumns.setString(3, constraint.getTableName());
	}

	@Override
	public String getDatabaseName() {
		// TODO Auto-generated method stub
		return null;
	}

}
